{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Overview","Independent Rates of Return","Correlated Rates of Return"],"not_rendered":{"charts":2,"images":0,"macros":1,"external_links":0,"defined_names":52,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Overview","index":0,"n_rows":24,"n_cols":2,"rows_kept":24,"cut":false,"hidden":false,"merged":[[0,1,0,7]]}
[null,"Finding a Minimum-Risk Portfolio"]
[]
[null,"The example on the next page illustrates the calculations which underlie the"]
[null,"Capital Asset Pricing Model (CAPM)."]
[]
[null,"There are three investment alternatives, and our goal is to find, for any"]
[null,"\"target\" expected rate of return, the portfolio for which the standard deviation"]
[null,"of the rate of return on the portfolio (i.e., the value in cell B12) is minimal."]
[]
[null,"I've set up \"Solver\" to solve the minimization problem, To set the target rate"]
[null,"of return, select the equality constraint in the Solver menu-box, and click on"]
[null,"\"Change\"."]
[]
[null,"I've also tabulated (by invoking Solver 11 separate times) the optimal"]
[null,"portfolios, and graphed the standard deviation of the rate-of-return of the"]
[null,"optimal portfolio below the table."]
[]
[null,"Note: Don't let the final tab of this workbook frighten you. I built it in order to"]
[null,"show that the same approach, with a bit more work, can also deal with"]
[null,"investment opportunities for which the rates of return are not independent."]
[null,"Not surprisingly, for any target rate of return, there's somewhat more risk in"]
[null,"the optimal portfolio than there was in the independent case: The risk can't"]
[null,"be as fully \"diversified away\" when the covariances in rates of return are all"]
[null,"positive."]

{"sheet":"Independent Rates of Return","index":1,"n_rows":32,"n_cols":10,"rows_kept":32,"cut":false,"hidden":false,"merged":[[0,1,0,6],[14,3,14,5],[28,7,28,8]]}
[null,"Independent Rates of Return"]
[]
[null,"budget",100]
[]
[null,null,"Investment A","Investment B","Investment C"]
[null,"E[ROR]",0.1,0.125,0.15]
[null,"Stdev[ROR]",0.03,0.04,0.05]
[]
[null,"$ invested",32.653061229326198,34.693877541537596,{"$f":"C3-SUM(C9:D9)","v":32.653061229136199}]
[]
[null,"E[portfolio ROR]",{"$f":"SUMPRODUCT(C6:E6,C9:E9)/C3","v":0.1249999999999525}]
[null,"Stdev[portfolio ROR]",{"$f":"SQRT(SUMPRODUCT(C9:E9^2,C7:E7^2))/C3","v":2.3560603574936993E-2}]
[null,null,null,null,null,null,null,null,"risk-free ROR"]
[null,null,null,null,null,null,null,null,7.0000000000000007E-2]
[null,null,"E[ROR]","minimum-risk portfolio that achieves\nthe target expected rate of return",null,null,"minimum\nstdev(ROR)"]
[null,null,0.1,100,0,0,0.03,null,{"$f":"(C16-$I$14)/G16","v":1}]
[null,null,0.105,80.000000000189942,19.999999999810044,0,2.5298221281377063E-2,null,1.3834964763220234]
[null,null,0.11,67.551020408276997,24.897959183635976,7.5510204080870267,2.2893720732558701E-2,null,1.7472039808327575]
[null,null,0.115,55.918367347006935,28.163265306176122,15.91836734681695,2.1718044787410198E-2,null,2.0720097246546887]
[null,null,0.12,44.285714285833137,31.428571428523657,24.285714285643209,2.195449840009113E-2,null,2.2774375933723197]
[null,null,0.125,32.653061229326198,34.693877541537596,32.653061229136199,2.3560603574936993E-2,null,2.3344053909767921,"market portfolio"]
[null,null,0.13,21.020408163152052,37.959183673505869,41.020408163342083,2.628649067179421E-2,null,2.2825412775384613]
[null,null,0.13500000000000001,9.3877551020313224,41.22448979574731,49.387755102221369,2.9826710396277538E-2,null,2.1792547396749522]
[null,null,0.14000000000000001,0,39.999999999810029,60.000000000189971,3.4000000000048054E-2,null,2.0588235294088548]
[null,null,0.14499999999999999,0,19.999999999810029,80.000000000189971,4.0792156108820518E-2,null,1.8385887669169485]
[null,null,0.15,0,0,100,0.05,null,1.5999999999999996]
[]
[]
[null,null,null,null,null,null,null,"Data for tangent line"]
[null,null,null,null,null,null,null,7.0000000000000007E-2,0]
[null,null,null,null,null,null,null,{"$f":"C21","v":0.125},{"$f":"G21","v":2.3560603574936993E-2}]
[null,null,null,null,null,null,null,{"$f":"H30+(H31-H30)*(I32/I31)","v":0.18672026954883963},0.05]

{"sheet":"Correlated Rates of Return","index":2,"n_rows":32,"n_cols":14,"rows_kept":32,"cut":false,"hidden":false,"merged":[[28,7,28,8],[0,1,0,6],[14,3,14,5],[2,6,2,8],[2,11,2,13]]}
[null,"Correlated Rates of Return"]
[]
[null,"budget",100,null,null,null,"  correlation matrix",null,null,null,null,"  covariance matrix"]
[null,null,null,null,null,null,{"$f":"C7","v":0.03},{"$f":"D7","v":0.04},{"$f":"E7","v":0.05},null,null,"A","B","C"]
[null,null,"Investment A","Investment B","Investment C",{"$f":"C7","v":0.03},1,0.2,0.3,null,"A",{"$f":"F5:F7*G4:I4*G5:I7","v":8.9999999999999998E-4},2.3999999999999998E-4,4.4999999999999999E-4]
[null,"E[ROR]",0.1,0.125,0.15,{"$f":"D7","v":0.04},0.2,1,0.45,null,"B",2.3999999999999998E-4,1.6000000000000001E-3,9.0000000000000008E-4]
[null,"Stdev[ROR]",0.03,0.04,0.05,{"$f":"E7","v":0.05},0.3,0.45,1,null,"C",4.4999999999999999E-4,9.0000000000000008E-4,2.5000000000000005E-3]
[]
[null,"$ invested",33.550488599070171,32.899022801669624,{"$f":"C3-SUM(C9:D9)","v":33.550488599260206}]
[]
[null,"E[portfolio ROR]",{"$f":"SUMPRODUCT(C6:E6,C9:E9)/C3","v":0.12500000000004749},{"$f":"showformula(C11)","v":"=SUMPRODUCT(C6:E6,C9:E9)/C3"}]
[null,"Stdev[portfolio ROR]",{"$f":"SQRT(MMULT(MMULT(C9:E9,L5:N7),TRANSPOSE(C9:E9)))/C3","v":3.0147303940068225E-2},{"$f":"showformula(C12)","v":"=SQRT(MMULT(MMULT(C9:E9,L5:N7),TRANSPOSE(C9:E9)))/C3"},null,null,null,null,"Excel's \"matrix\" commands simplify this."]
[null,null,{"$f":"SQRT(C9^2*C7^2+D9^2*D7^2+E9^2*E7^2+2*(C9*D9*H5*C7*D7+C9*E9*I5*C7*E7+D9*E9*I6*D7*E7))/C3","v":3.0147303940068225E-2},{"$f":"showformula(C13)","v":"=SQRT(C9^2*C7^2+D9^2*D7^2+E9^2*E7^2+2*(C9*D9*H5*C7*D7+C9*E9*I5*C7*E7+D9*E9*I6*D7*E7))/C3"}]
[null,null,null,null,null,null,null,null,"risk-free ROR"]
[null,null,"E[ROR]","minimum-risk portfolio that achieves\nthe target expected rate of return",null,null,"minimum\nstdev(ROR)",null,7.0000000000000007E-2]
[null,null,0.1,100.000001,0,-9.9999999747524271E-7,3.0000000150000004E-2,null,{"$f":"(C16-$I$15)/G16","v":0.99999999499999981}]
[null,null,0.105,80.00000186245498,19.99999813754502,0,2.677312102717519E-2,null,{"$f":"(C17-$I$15)/G17","v":1.3072812827639473}]
[null,null,0.11,64.918566775337425,30.162866449135226,4.9185667755273528,2.6083441650571672E-2,null,1.5335399574895943]
[null,null,0.115,54.462540716569528,31.074918566670984,14.462540716759491,2.658519585101771E-2,null,1.6926713744061941]
[null,null,0.12,44.006514657879926,31.986970684050213,24.006514658069861,2.7981136037379319E-2,null,1.7869181556176348]
[null,null,0.125,33.550488599070171,32.899022801669624,33.550488599260206,3.0147303940068225E-2,null,1.8243754104625094,"market portfolio"]
[null,null,0.13,23.094462546024339,33.811074906651193,43.094462547324468,3.2932059170141102E-2,null,1.8219328372396739]
[null,null,0.13500000000000001,12.638436481944526,34.723127035920953,52.638436482134523,3.6192896166256171E-2,null,1.7959325416074783]
[null,null,0.14000000000000001,2.1824104240294604,35.635179151751124,62.182410424219412,3.9813007548230049E-2,null,1.7582193436454405]
[null,null,0.14499999999999999,0,19.99999999980998,80.000000000190028,4.4181444068798079E-2,null,1.697545238295338]
[null,null,0.15,0,0,100,0.05,null,1.5999999999999996]
[]
[]
[null,null,null,null,null,null,null,"Data for tangent line"]
[null,null,null,null,null,null,null,7.0000000000000007E-2,0]
[null,null,null,null,null,null,null,{"$f":"C21","v":0.125},{"$f":"G21","v":3.0147303940068225E-2}]
[null,null,null,null,null,null,null,{"$f":"H30+(H31-H30)*(I32/I31)","v":0.16121877052312547},0.05]